/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package data;

import com.mysql.jdbc.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import on.*;
import util.Errors;

/**
 *
 * Author: Cristina Torres Garcia
 */
public class DataLocals extends DataManager<Locals> {

    private String tabla = "Local ";
    private String tabla2= "Club ";

    public DataLocals() {
        super();
        setTabla(tabla);
        requerir();
    }

    @Override
    public void requerir() {
        try {
            //conectar con la bd
            sql.conectar();
            //recogemos los datos de la bd
            ResultSet rs = (ResultSet) sql.ejecutarSelect(
                    "select id_local,id_club,local.name,long_local,wide_local,high_local," +
                    "long_area,wide_area,high_area,bottom_wall,sidewall," +
                    "lighting,local.adreess,local.city,local.cp,local.province,local.phone,local.cell_phone,local.fax,local.email," +
                    "mark_table,color_table,soil_color ,club.name from " + tabla +" join "+ tabla2 + " using(id_club) ");
            while (rs.next()) {
                Locals local = new Locals();
                local.setId_local(rs.getInt(1));
                local.setId_club(rs.getInt(2));
                local.setNombre(rs.getString(3));
                local.setLocalLargo(rs.getString(4));
                local.setLocalAlto(rs.getString(5));
                local.setLocalAncho(rs.getString(6));
                local.setTerrenoLargo(rs.getString(7));
                local.setTerrenoAlto(rs.getString(8));
                local.setTerrenoAncho(rs.getString(9));
                local.setColorParedesFondo(rs.getString(10));
                local.setColorParedesLaterales(rs.getString(11));
                local.setIluminacion(rs.getString(12));
                local.setDireccionLocal(rs.getString(13));
                local.setPoblacion(rs.getString(14));
                local.setCodigoPostal(rs.getString(15));
                local.setProvincia(rs.getString(16));
                local.setTelefono(rs.getString(17));
                local.setMovil(rs.getString(18));
                local.setFax(rs.getString(19));
                local.setEMail(rs.getString(20));
                local.setMarcaMesa(rs.getString(21));
                local.setColorMesa(rs.getString(22));
                local.setColorSuelo(rs.getString(23));
                local.setClubNombre(rs.getString(24));
                getList().add(local);
            }

        } catch (SQLException ex) {
            Errors.creaError("ERROR: No se han podido conseguir los datos\n" + ex.getMessage(), ex.getStackTrace());
        } finally {
            sql.desconectar();
        }
    }


    @Override
    public void guardar(Locals o) {

        getList().set(index, o);
        sql.conectar();
        sql.ejectarUpdate("UPDATE " + tabla + " " +
                "SET id_club=" + o.getId_club() + "," +
                " name=\'" + o.getNombre() + "\'," +
                " long_local=" + o.getLocalLargo() + "," +
                " wide_local=" + o.getLocalAlto() + "," +
                " high_local=" + o.getLocalAlto() + "," +
                " long_area=" + o.getTerrenoLargo() + "," +
                " wide_area=" + o.getTerrenoAlto() + "," +
                " high_area=" + o.getTerrenoAncho() + "," +
                " bottom_wall=\'" + o.getColorParedesFondo() + "\'," +
                " sidewall=\'" + o.getColorParedesLaterales() + "\'," +
                " lighting=" + o.getIluminacion() + "," +
                " adreess=\'" + o.getDireccionLocal() + "\'," +
                " city=\'" + o.getPoblacion() + "\'," +
                " cp=" +o.getCodigoPostal() +","+
                " province=\'" + o.getProvincia() + "\'," +
                " phone=" + o.getTelefono() + "," +
                " cell_phone=" + o.getMovil() + "," +
                " fax=" + o.getFax() + "," +
                " email=\'" + o.getEMail() + "\'," +
                " mark_table=\'" + o.getMarcaMesa() + "\'," +
                " color_table=\'" + o.getColorMesa() + "\'," +
                " soil_color=\'" + o.getColorSuelo() +"\'"+
                " WHERE id_local = "+o.getId_local());
        sql.desconectar();
    }

    @Override
    public void insertar(Locals o) {

        sql.conectar();
        getList().add(o);
        sql.ejectarUpdate("INSERT INTO " + tabla + " (id_club,name,long_local,wide_local,high_local," +
                "long_area,wide_area,high_area,bottom_wall,sidewall," +
                "lighting,adreess,city,cp,province,phone,cell_phone,fax,email," +
                "mark_table,color_table,soil_color)" +
                " VALUES( " + o.getId_club() + "," +
                "\'" + o.getNombre() + "\'," +
                "\'" + o.getLocalLargo() + "\'," +
                "\'" + o.getLocalAlto() + "\'," +
                "\'" + o.getLocalAlto() + "\'," +
                "\'" + o.getTerrenoLargo() + "\'," +
                "\'" + o.getTerrenoAlto() + "\'," +
                "\'" + o.getTerrenoAncho() + "\'," +
                "\'" + o.getColorParedesFondo() + "\'," +
                "\'" + o.getColorParedesLaterales() + "\'," +
                "\'" + o.getIluminacion() + "\'," +
                "\'" + o.getDireccionLocal() + "\'," +
                "\'" + o.getPoblacion() + "\'," +
                "\'" + o.getCodigoPostal() +"\',"+
                "\'" + o.getProvincia() + "\'," +
                "\'" + o.getTelefono() + "\'," +
                "\'" + o.getMovil() + "\'," +
                "\'" + o.getFax() + "\'," +
                "\'" + o.getEMail() + "\'," +
                "\'" + o.getMarcaMesa() + "\'," +
                "\'" + o.getColorMesa() + "\'," +
                "\'" + o.getColorSuelo() + "\')");

        sql.desconectar();

    }

    @Override
    public void eliminar(Locals o) {
        sql.conectar();
        getList().remove(index);
        sql.ejectarUpdate("DELETE FROM " + tabla + " " +
                "WHERE id_local=" + o.getId_local());
        sql.desconectar();
    }

    @Override
    public void crear(String tabla) {
         sql.conectar();
        sql.ejectarUpdate("DROP TABLE IF EXISTS " + tabla + ";");
        sql.ejectarUpdate("CREATE TABLE IF NOT EXISTS " + tabla + "(" +
                "id_local int(11) NOT NULL,"+
                "id_club int(11) NOT NULL," +
                "name varchar(30) NOT NULL,"+
                "long_local int(11) NOT NULL,"+
                "wide_lcal int(11) NOT NULL,"+
                "high_local int(11) NOT NULL,"+
                "long_area int(11) NOT NULL,"+
                "wide_area int(11) NOT NULL,"+
                "high_area int(11) NOT NULL,"+
                "bottom_wall varchar(15) NOT NUL,"+
                "sidewall varchar(15) NOT  NULL,"+
                "lighting varchar(20) NULL,"+
                "adreess varchar(30) NOT NULL,"+
                "city varchar(30) NOT NULL,"+
                "cp int(11) NOT NULL,"+
                "province varchar(30) NOT NULL,"+
                "phone int(11) NOT NULL,"+
                "cell_phone int(11)  NULL,"+
                "fax int(11) NULL,"+
                "email varchar(40) NULL,"+
                "mark_table varchar(15) NULL,"+
                "color_table varchar(15) NULL,"+
                "soil_color varchar(15) NULL,"+
                "PRIMARY KEY (id_local) )"
               );
        sql.desconectar();

    }

    public ArrayList<Clubs> obtenerClub(){
      ArrayList<Clubs> l =new ArrayList<Clubs>();
     try{
     sql.conectar();
     //getList().remove(index);
       ResultSet rs = (ResultSet) sql.ejecutarSelect("SELECT * FROM " + tabla2 );
       int i=0;
       while(rs.next()){
              Clubs club = new Clubs();
                club.setId_Clubs(rs.getInt(1));
//                club.setNumero(rs.getInt(2));
                club.setNombre(rs.getString(3));
//                club.setDni(rs.getInt(4));
//                club.setPersonaContacto(rs.getString(5));
//                club.setDireccion(rs.getString(6));
//                club.setPoblacion(rs.getString(7));
         l.add(club);
       }
     }catch(SQLException ex){
        Errors.creaError("ERROR: No se han podido conseguir los datos\n" + ex.getMessage(), ex.getStackTrace());
     }finally{
         sql.desconectar();
         return l;
     }

    }

}
